<!DOCTYPE html>
<html lang="zh-CN">
<head>
  <meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=2">
<meta name="theme-color" content="#222">
<meta name="generator" content="Hexo 5.4.0">
  <link rel="apple-touch-icon" sizes="180x180" href="/images/apple-touch-icon-next.png">
  <link rel="icon" type="image/png" sizes="32x32" href="/images/favicon-32x32-next.png">
  <link rel="icon" type="image/png" sizes="16x16" href="/images/favicon-16x16-next.png">
  <link rel="mask-icon" href="/images/logo.svg" color="#222">

<link rel="stylesheet" href="/css/main.css">


<link rel="stylesheet" href="/lib/font-awesome/css/all.min.css">

<script id="hexo-configurations">
    var NexT = window.NexT || {};
    var CONFIG = {"hostname":"wan-lei8.gitee.io","root":"/","scheme":"Pisces","version":"7.8.0","exturl":false,"sidebar":{"position":"left","display":"post","padding":18,"offset":12,"onmobile":false},"copycode":{"enable":false,"show_result":false,"style":null},"back2top":{"enable":true,"sidebar":false,"scrollpercent":false},"bookmark":{"enable":false,"color":"#222","save":"auto"},"fancybox":false,"mediumzoom":false,"lazyload":false,"pangu":false,"comments":{"style":"tabs","active":null,"storage":true,"lazyload":false,"nav":null},"algolia":{"hits":{"per_page":10},"labels":{"input_placeholder":"Search for Posts","hits_empty":"We didn't find any results for the search: ${query}","hits_stats":"${hits} results found in ${time} ms"}},"localsearch":{"enable":false,"trigger":"auto","top_n_per_article":1,"unescape":false,"preload":false},"motion":{"enable":true,"async":false,"transition":{"post_block":"fadeIn","post_header":"slideDownIn","post_body":"slideDownIn","coll_header":"slideLeftIn","sidebar":"slideUpIn"}}};
  </script>

  <meta name="description" content="1. 使用group by的简单例子group by一般用于分组统计，它表达的逻辑就是根据一定的规则，进行分组。我们先从一个简单的例子，一起来复习一下哈。 假设用一张员工表，表结构如下：">
<meta property="og:type" content="article">
<meta property="og:title" content="group by 详解">
<meta property="og:url" content="https://wan-lei8.gitee.io/2022/03/10/group-by-%E8%AF%A6%E8%A7%A3/index.html">
<meta property="og:site_name" content="程序员西桐">
<meta property="og:description" content="1. 使用group by的简单例子group by一般用于分组统计，它表达的逻辑就是根据一定的规则，进行分组。我们先从一个简单的例子，一起来复习一下哈。 假设用一张员工表，表结构如下：">
<meta property="og:locale" content="zh_CN">
<meta property="og:image" content="https://mmbiz.qpic.cn/mmbiz_png/PoF8jo1Pmpw3lfjrm2iavcLbJViaxTt98D2B4E3XgIU1er1ThLArqYQRE3krwUhquBIgJVibkQSSkvAwq2MyTouFA/640?wx_fmt=png&wxfrom=5&wx_lazy=1&wx_co=1">
<meta property="og:image" content="https://mmbiz.qpic.cn/mmbiz_png/PoF8jo1Pmpw3lfjrm2iavcLbJViaxTt98DbSSbDarNcqbcybRW3Eq3HXKgHwt0TyQ6qmNkV6t87AlIHtl2JS4dLA/640?wx_fmt=png&wxfrom=5&wx_lazy=1&wx_co=1">
<meta property="og:image" content="https://mmbiz.qpic.cn/mmbiz_png/PoF8jo1Pmpw3lfjrm2iavcLbJViaxTt98DSYUTxfiaoZA6vkN0mjeCblTtOwns0ydiaJpZK7WrltEr1NsWvrk8dCzA/640?wx_fmt=png&wxfrom=5&wx_lazy=1&wx_co=1">
<meta property="og:image" content="https://mmbiz.qpic.cn/mmbiz_png/PoF8jo1Pmpw3lfjrm2iavcLbJViaxTt98DN46MTciaOknet4Gib07uUguZiaVyXtTm9RZfWBziaKia7xqERAw9mp8drDg/640?wx_fmt=png&wxfrom=5&wx_lazy=1&wx_co=1">
<meta property="og:image" content="https://mmbiz.qpic.cn/mmbiz_png/PoF8jo1Pmpw3lfjrm2iavcLbJViaxTt98DkVZibUZMgZQC2OEicCvUZxQ5y0XO1KgUZzyibtJ1qib9G6rFC2ZzejlGUw/640?wx_fmt=png&wxfrom=5&wx_lazy=1&wx_co=1">
<meta property="og:image" content="https://mmbiz.qpic.cn/mmbiz_png/PoF8jo1Pmpw3lfjrm2iavcLbJViaxTt98DruiceXEP2Z44xesnF6cYngaic9O1S36jsVRUvytib8icGEvTYsAwbOKXiaw/640?wx_fmt=png&wxfrom=5&wx_lazy=1&wx_co=1">
<meta property="og:image" content="https://mmbiz.qpic.cn/mmbiz_png/PoF8jo1Pmpw3lfjrm2iavcLbJViaxTt98D8D93fARUOWxwMAqkLNGxwEsVRfbFIib9ljlPWoWI3Dtvb6GZ4elPqBQ/640?wx_fmt=png&wxfrom=5&wx_lazy=1&wx_co=1">
<meta property="og:image" content="https://mmbiz.qpic.cn/mmbiz_png/PoF8jo1Pmpw3lfjrm2iavcLbJViaxTt98DiaygageSOJ2vegzFd9376EnPCtT9PkqvXHfJW292RorMBld9bEN56pg/640?wx_fmt=png&wxfrom=5&wx_lazy=1&wx_co=1">
<meta property="og:image" content="https://mmbiz.qpic.cn/mmbiz_png/PoF8jo1Pmpw3lfjrm2iavcLbJViaxTt98DkCukib9KOVs3bYlmZibOK5kzhUCS5NdUic0dMYhelwTITg40NKQfvibGHA/640?wx_fmt=png&wxfrom=5&wx_lazy=1&wx_co=1">
<meta property="og:image" content="https://mmbiz.qpic.cn/mmbiz_png/PoF8jo1Pmpw3lfjrm2iavcLbJViaxTt98D5bI3xxzpRudNexl6sSoV8nNwdhgcsvzGoZev3oecHgFPTicmyzIiaARg/640?wx_fmt=png&wxfrom=5&wx_lazy=1&wx_co=1">
<meta property="og:image" content="https://mmbiz.qpic.cn/mmbiz_png/PoF8jo1Pmpw3lfjrm2iavcLbJViaxTt98DMD7yicibUeXwiaP5iaap3Zjt0F9yItK1bM9pQ4toN59zbd4K8oKWMHEeYQ/640?wx_fmt=png&wxfrom=5&wx_lazy=1&wx_co=1">
<meta property="og:image" content="https://mmbiz.qpic.cn/mmbiz_png/PoF8jo1Pmpw3lfjrm2iavcLbJViaxTt98DiaxicR4nic6laxXgrVFJAGwq5lU7FJSvRicZJKP6lGUxONek0tKPtAYAfA/640?wx_fmt=png&wxfrom=5&wx_lazy=1&wx_co=1">
<meta property="og:image" content="https://mmbiz.qpic.cn/mmbiz_png/PoF8jo1Pmpw3lfjrm2iavcLbJViaxTt98DxE8wviaAbjPONoicfnXhdaDWKrqOWm60JfPvlBCgdO5ib8Nw6wiavEUA1w/640?wx_fmt=png&wxfrom=5&wx_lazy=1&wx_co=1">
<meta property="article:published_time" content="2022-03-10T03:23:13.000Z">
<meta property="article:modified_time" content="2022-03-10T03:25:08.890Z">
<meta name="twitter:card" content="summary">
<meta name="twitter:image" content="https://mmbiz.qpic.cn/mmbiz_png/PoF8jo1Pmpw3lfjrm2iavcLbJViaxTt98D2B4E3XgIU1er1ThLArqYQRE3krwUhquBIgJVibkQSSkvAwq2MyTouFA/640?wx_fmt=png&wxfrom=5&wx_lazy=1&wx_co=1">

<link rel="canonical" href="https://wan-lei8.gitee.io/2022/03/10/group-by-%E8%AF%A6%E8%A7%A3/">


<script id="page-configurations">
  // https://hexo.io/docs/variables.html
  CONFIG.page = {
    sidebar: "",
    isHome : false,
    isPost : true,
    lang   : 'zh-CN'
  };
</script>

  <title>group by 详解 | 程序员西桐</title>
  






  <noscript>
  <style>
  .use-motion .brand,
  .use-motion .menu-item,
  .sidebar-inner,
  .use-motion .post-block,
  .use-motion .pagination,
  .use-motion .comments,
  .use-motion .post-header,
  .use-motion .post-body,
  .use-motion .collection-header { opacity: initial; }

  .use-motion .site-title,
  .use-motion .site-subtitle {
    opacity: initial;
    top: initial;
  }

  .use-motion .logo-line-before i { left: initial; }
  .use-motion .logo-line-after i { right: initial; }
  </style>
</noscript>

</head>

<body itemscope itemtype="http://schema.org/WebPage">
  <div class="container use-motion">
    <div class="headband"></div>

    <header class="header" itemscope itemtype="http://schema.org/WPHeader">
      <div class="header-inner"><div class="site-brand-container">
  <div class="site-nav-toggle">
    <div class="toggle" aria-label="切换导航栏">
      <span class="toggle-line toggle-line-first"></span>
      <span class="toggle-line toggle-line-middle"></span>
      <span class="toggle-line toggle-line-last"></span>
    </div>
  </div>

  <div class="site-meta">

    <a href="/" class="brand" rel="start">
      <span class="logo-line-before"><i></i></span>
      <h1 class="site-title">程序员西桐</h1>
      <span class="logo-line-after"><i></i></span>
    </a>
      <p class="site-subtitle" itemprop="description">但行好事，莫问前程</p>
  </div>

  <div class="site-nav-right">
    <div class="toggle popup-trigger">
    </div>
  </div>
</div>




<nav class="site-nav">
  <ul id="menu" class="main-menu menu">
        <li class="menu-item menu-item-home">

    <a href="/" rel="section"><i class="fa fa-home fa-fw"></i>首页</a>

  </li>
        <li class="menu-item menu-item-tags">

    <a href="/tags/" rel="section"><i class="fa fa-tags fa-fw"></i>标签</a>

  </li>
        <li class="menu-item menu-item-categories">

    <a href="/categories/" rel="section"><i class="fa fa-th fa-fw"></i>分类</a>

  </li>
  </ul>
</nav>




</div>
    </header>

    
  <div class="back-to-top">
    <i class="fa fa-arrow-up"></i>
    <span>0%</span>
  </div>


    <main class="main">
      <div class="main-inner">
        <div class="content-wrap">
          

          <div class="content post posts-expand">
            

    
  
  
  <article itemscope itemtype="http://schema.org/Article" class="post-block" lang="zh-CN">
    <link itemprop="mainEntityOfPage" href="https://wan-lei8.gitee.io/2022/03/10/group-by-%E8%AF%A6%E8%A7%A3/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="image" content="/images/avatar.png">
      <meta itemprop="name" content="">
      <meta itemprop="description" content="">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="程序员西桐">
    </span>
      <header class="post-header">
        <h1 class="post-title" itemprop="name headline">
          group by 详解
        </h1>

        <div class="post-meta">
            <span class="post-meta-item">
              <span class="post-meta-item-icon">
                <i class="far fa-calendar"></i>
              </span>
              <span class="post-meta-item-text">发表于</span>
              

              <time title="创建时间：2022-03-10 11:23:13 / 修改时间：11:25:08" itemprop="dateCreated datePublished" datetime="2022-03-10T11:23:13+08:00">2022-03-10</time>
            </span>

          

        </div>
      </header>

    
    
    
    <div class="post-body" itemprop="articleBody">

      
        <h2 id="1-使用group-by的简单例子"><a href="#1-使用group-by的简单例子" class="headerlink" title="1. 使用group by的简单例子"></a>1. 使用group by的简单例子</h2><p><code>group by</code>一般用于<strong>分组统计</strong>，它表达的逻辑就是<code>根据一定的规则，进行分组</code>。我们先从一个简单的例子，一起来复习一下哈。</p>
<p>假设用一张员工表，表结构如下：</p>
<span id="more"></span>

<figure class="highlight n1ql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> TABLE <span class="symbol">`staff`</span> (</span><br><span class="line">  <span class="symbol">`id`</span> bigint(<span class="number">11</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span> AUTO_INCREMENT COMMENT <span class="string">&#x27;主键id&#x27;</span>,</span><br><span class="line">  <span class="symbol">`id_card`</span> varchar(<span class="number">20</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span> COMMENT <span class="string">&#x27;身份证号码&#x27;</span>,</span><br><span class="line">  <span class="symbol">`name`</span> varchar(<span class="number">64</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span> COMMENT <span class="string">&#x27;姓名&#x27;</span>,</span><br><span class="line">  <span class="symbol">`age`</span> int(<span class="number">4</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span> COMMENT <span class="string">&#x27;年龄&#x27;</span>,</span><br><span class="line">  <span class="symbol">`city`</span> varchar(<span class="number">64</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span> COMMENT <span class="string">&#x27;城市&#x27;</span>,</span><br><span class="line">  <span class="keyword">PRIMARY</span> <span class="keyword">KEY</span> (<span class="symbol">`id`</span>)</span><br><span class="line">) ENGINE=InnoDB AUTO_INCREMENT=<span class="number">15</span> DEFAULT CHARSET=utf8 COMMENT=<span class="string">&#x27;员工表&#x27;</span>;</span><br></pre></td></tr></table></figure>

<p>表存量的数据如下：</p>
<p><img src="https://mmbiz.qpic.cn/mmbiz_png/PoF8jo1Pmpw3lfjrm2iavcLbJViaxTt98D2B4E3XgIU1er1ThLArqYQRE3krwUhquBIgJVibkQSSkvAwq2MyTouFA/640?wx_fmt=png&wxfrom=5&wx_lazy=1&wx_co=1" alt="图片"></p>
<p>我们现在有这么一个需求：<strong>统计每个城市的员工数量</strong>。对应的 SQL 语句就可以这么写：</p>
<figure class="highlight axapta"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> city ,<span class="keyword">count</span>(*) <span class="keyword">as</span> num <span class="keyword">from</span> staff <span class="keyword">group</span> <span class="keyword">by</span> city;</span><br></pre></td></tr></table></figure>

<p>执行结果如下：</p>
<p><img src="https://mmbiz.qpic.cn/mmbiz_png/PoF8jo1Pmpw3lfjrm2iavcLbJViaxTt98DbSSbDarNcqbcybRW3Eq3HXKgHwt0TyQ6qmNkV6t87AlIHtl2JS4dLA/640?wx_fmt=png&wxfrom=5&wx_lazy=1&wx_co=1" alt="图片"></p>
<p>这条SQL语句的逻辑很清楚啦，但是它的底层执行流程是怎样的呢？</p>
<h2 id="2-group-by-原理分析"><a href="#2-group-by-原理分析" class="headerlink" title="2. group by 原理分析"></a>2. group by 原理分析</h2><h3 id="2-1-explain-分析"><a href="#2-1-explain-分析" class="headerlink" title="2.1 explain 分析"></a>2.1 explain 分析</h3><p>我们先用<code>explain</code>查看一下执行计划</p>
<figure class="highlight n1ql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">explain</span> <span class="keyword">select</span> city ,<span class="built_in">count</span>(*) <span class="keyword">as</span> num <span class="keyword">from</span> staff <span class="keyword">group</span> <span class="keyword">by</span> city;</span><br></pre></td></tr></table></figure>

<p><img src="https://mmbiz.qpic.cn/mmbiz_png/PoF8jo1Pmpw3lfjrm2iavcLbJViaxTt98DSYUTxfiaoZA6vkN0mjeCblTtOwns0ydiaJpZK7WrltEr1NsWvrk8dCzA/640?wx_fmt=png&wxfrom=5&wx_lazy=1&wx_co=1" alt="图片"></p>
<ul>
<li>Extra 这个字段的<code>Using temporary</code>表示在执行分组的时候使用了<strong>临时表</strong></li>
<li>Extra 这个字段的<code>Using filesort</code>表示使用了<strong>排序</strong></li>
</ul>
<p><code>group by</code> 怎么就使用到<code>临时表和排序</code>了呢？我们来看下这个SQL的执行流程</p>
<h3 id="2-2-group-by-的简单执行流程"><a href="#2-2-group-by-的简单执行流程" class="headerlink" title="2.2 group by 的简单执行流程"></a>2.2 group by 的简单执行流程</h3><figure class="highlight n1ql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">explain</span> <span class="keyword">select</span> city ,<span class="built_in">count</span>(*) <span class="keyword">as</span> num <span class="keyword">from</span> staff <span class="keyword">group</span> <span class="keyword">by</span> city;</span><br></pre></td></tr></table></figure>

<p>我们一起来看下这个SQL的执行流程哈</p>
<ol>
<li>创建内存临时表，表里有两个字段<code>city</code>和<code>num</code>；</li>
<li>全表扫描<code>staff</code>的记录，依次取出city = ‘X’的记录。</li>
</ol>
<ul>
<li>判断<strong>临时表</strong>中是否有为 city=’X’的行，没有就插入一个记录 (X,1);</li>
<li>如果临时表中有city=’X’的行的行，就将x 这一行的num值加 1；</li>
</ul>
<ol>
<li>遍历完成后，再根据字段<code>city</code>做<strong>排序</strong>，得到结果集返回给客户端。</li>
</ol>
<p>这个流程的执行图如下：</p>
<p><img src="https://mmbiz.qpic.cn/mmbiz_png/PoF8jo1Pmpw3lfjrm2iavcLbJViaxTt98DN46MTciaOknet4Gib07uUguZiaVyXtTm9RZfWBziaKia7xqERAw9mp8drDg/640?wx_fmt=png&wxfrom=5&wx_lazy=1&wx_co=1" alt="图片"></p>
<p>临时表的排序是怎样的呢？</p>
<blockquote>
<p>就是把需要排序的字段，放到sort buffer，排完就返回。在这里注意一点哈，排序分<strong>全字段排序</strong>和<strong>rowid排序</strong></p>
<ul>
<li>如果是<code>全字段排序</code>，需要查询返回的字段，都放入<code>sort buffer</code>，根据<strong>排序字段</strong>排完，直接返回</li>
<li>如果是<code>rowid排序</code>，只是需要排序的字段放入<code>sort buffer</code>，然后多一次<strong>回表</strong>操作，再返回。</li>
<li>怎么确定走的是全字段排序还是rowid 排序排序呢？由一个数据库参数控制的，<code>max_length_for_sort_data</code></li>
</ul>
</blockquote>
<h2 id="3-where-和-having的区别"><a href="#3-where-和-having的区别" class="headerlink" title="3. where 和 having的区别"></a>3. where 和 having的区别</h2><ul>
<li>group by + where 的执行流程</li>
<li>group by + having 的执行流程</li>
<li>同时有where、group by 、having的执行顺序</li>
</ul>
<h3 id="3-1-group-by-where-的执行流程"><a href="#3-1-group-by-where-的执行流程" class="headerlink" title="3.1 group by + where 的执行流程"></a>3.1 group by + where 的执行流程</h3><p>有些小伙伴觉得上一小节的SQL太简单啦，如果加了<strong>where条件</strong>之后，并且where条件列加了索引呢，<strong>执行流程是怎样</strong>？</p>
<p>好的，我们给它加个条件，并且加个<code>idx_age</code>的索引，如下：</p>
<figure class="highlight pgsql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> city ,count(*) <span class="keyword">as</span> num <span class="keyword">from</span> staff <span class="keyword">where</span> age&gt; <span class="number">30</span> <span class="keyword">group</span> <span class="keyword">by</span> city;</span><br><span class="line">//加索引</span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> staff <span class="keyword">add</span> <span class="keyword">index</span> idx_age (age);</span><br></pre></td></tr></table></figure>

<p>再来expain分析一下：</p>
<figure class="highlight n1ql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">explain</span> <span class="keyword">select</span> city ,<span class="built_in">count</span>(*) <span class="keyword">as</span> num <span class="keyword">from</span> staff <span class="keyword">where</span> age&gt; <span class="number">30</span> <span class="keyword">group</span> <span class="keyword">by</span> city;</span><br></pre></td></tr></table></figure>

<p><img src="https://mmbiz.qpic.cn/mmbiz_png/PoF8jo1Pmpw3lfjrm2iavcLbJViaxTt98DkVZibUZMgZQC2OEicCvUZxQ5y0XO1KgUZzyibtJ1qib9G6rFC2ZzejlGUw/640?wx_fmt=png&wxfrom=5&wx_lazy=1&wx_co=1" alt="图片"></p>
<p>从explain 执行计划结果，可以发现查询条件命中了<code>idx_age</code>的索引，并且使用了<code>临时表和排序</code></p>
<blockquote>
<p><strong>Using index condition</strong>:表示索引下推优化，根据索引尽可能的过滤数据,然后再返回给服务器层根据where其他条件进行过滤。这里单个索引为什么会出现索引下推呢？explain出现并不代表一定是使用了索引下推，只是代表可以使用，但是不一定用了。大家如果有想法或者有疑问，可以加我微信讨论哈。</p>
</blockquote>
<p>执行流程如下：</p>
<ol>
<li>创建内存临时表，表里有两个字段<code>city</code>和<code>num</code>；</li>
<li>扫描索引树<code>idx_age</code>，找到大于年龄大于30的主键ID</li>
<li>通过主键ID，回表找到city = ‘X’</li>
</ol>
<ul>
<li>判断<strong>临时表</strong>中是否有为 city=’X’的行，没有就插入一个记录 (X,1);</li>
<li>如果临时表中有city=’X’的行的行，就将x 这一行的num值加 1；</li>
</ul>
<ol>
<li>继续重复2,3步骤，找到所有满足条件的数据，</li>
<li>最后根据字段<code>city</code>做<strong>排序</strong>，得到结果集返回给客户端。</li>
</ol>
<h3 id="3-2-group-by-having-的执行"><a href="#3-2-group-by-having-的执行" class="headerlink" title="3.2 group by + having 的执行"></a>3.2 group by + having 的执行</h3><p>如果你要查询每个城市的员工数量，获取到员工数量不低于3的城市，having可以很好解决你的问题，SQL酱紫写：</p>
<figure class="highlight n1ql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> city ,<span class="built_in">count</span>(*) <span class="keyword">as</span> num <span class="keyword">from</span> staff  <span class="keyword">group</span> <span class="keyword">by</span> city <span class="keyword">having</span> num &gt;= <span class="number">3</span>;</span><br></pre></td></tr></table></figure>

<p>查询结果如下：</p>
<p><img src="https://mmbiz.qpic.cn/mmbiz_png/PoF8jo1Pmpw3lfjrm2iavcLbJViaxTt98DruiceXEP2Z44xesnF6cYngaic9O1S36jsVRUvytib8icGEvTYsAwbOKXiaw/640?wx_fmt=png&wxfrom=5&wx_lazy=1&wx_co=1" alt="图片"><code>having</code>称为分组过滤条件，它对返回的结果集操作。</p>
<h3 id="3-3-同时有where、group-by-、having的执行顺序"><a href="#3-3-同时有where、group-by-、having的执行顺序" class="headerlink" title="3.3 同时有where、group by 、having的执行顺序"></a>3.3 同时有where、group by 、having的执行顺序</h3><p>如果一个SQL同时含有<code>where、group by、having</code>子句，执行顺序是怎样的呢。</p>
<p>比如这个SQL：</p>
<figure class="highlight n1ql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> city ,<span class="built_in">count</span>(*) <span class="keyword">as</span> num <span class="keyword">from</span> staff  <span class="keyword">where</span> age&gt; <span class="number">19</span> <span class="keyword">group</span> <span class="keyword">by</span> city <span class="keyword">having</span> num &gt;= <span class="number">3</span>;</span><br></pre></td></tr></table></figure>

<ol>
<li>执行<code>where</code>子句查找符合年龄大于19的员工数据</li>
<li><code>group by</code>子句对员工数据，根据城市分组。</li>
<li>对<code>group by</code>子句形成的城市组，运行聚集函数计算每一组的员工数量值；</li>
<li>最后用<code>having</code>子句选出员工数量大于等于3的城市组。</li>
</ol>
<h3 id="3-4-where-having-区别总结"><a href="#3-4-where-having-区别总结" class="headerlink" title="3.4 where + having 区别总结"></a>3.4 where + having 区别总结</h3><ul>
<li><code>having</code>子句用于<strong>分组后筛选</strong>，where子句用于<strong>行</strong>条件筛选</li>
<li><code>having</code>一般都是配合<code>group by</code> 和聚合函数一起出现如(<code>count(),sum(),avg(),max(),min()</code>)</li>
<li><code>where</code>条件子句中不能使用聚集函数，而<code>having</code>子句就可以。</li>
<li><code>having</code>只能用在group by之后，where执行在group by之前</li>
</ul>
<h2 id="4-使用-group-by-注意的问题"><a href="#4-使用-group-by-注意的问题" class="headerlink" title="4. 使用 group by 注意的问题"></a>4. 使用 group by 注意的问题</h2><p>使用group by 主要有这几点需要注意：</p>
<ul>
<li><code>group by</code>一定要配合聚合函数一起使用嘛？</li>
<li><code>group by</code>的字段一定要出现在select中嘛</li>
<li><code>group by</code>导致的慢SQL问题</li>
</ul>
<h3 id="4-1-group-by一定要配合聚合函数使用嘛？"><a href="#4-1-group-by一定要配合聚合函数使用嘛？" class="headerlink" title="4.1 group by一定要配合聚合函数使用嘛？"></a>4.1 group by一定要配合聚合函数使用嘛？</h3><p>group by 就是<strong>分组统计</strong>的意思，一般情况都是配合聚合函数<code>如（count(),sum(),avg(),max(),min())</code>一起使用。</p>
<ul>
<li>count() 数量</li>
<li>sum() 总和</li>
<li>avg() 平均</li>
<li>max() 最大值</li>
<li>min() 最小值</li>
</ul>
<p>如果没有配合聚合函数使用可以吗？</p>
<blockquote>
<p>我用的是<strong>Mysql 5.7</strong> ，是可以的。不会报错，并且返回的是，分组的第一行数据。</p>
</blockquote>
<p>比如这个SQL：</p>
<figure class="highlight axapta"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> city,id_card,age <span class="keyword">from</span> staff <span class="keyword">group</span> <span class="keyword">by</span>  city;</span><br></pre></td></tr></table></figure>

<p>查询结果是</p>
<p><img src="https://mmbiz.qpic.cn/mmbiz_png/PoF8jo1Pmpw3lfjrm2iavcLbJViaxTt98D8D93fARUOWxwMAqkLNGxwEsVRfbFIib9ljlPWoWI3Dtvb6GZ4elPqBQ/640?wx_fmt=png&wxfrom=5&wx_lazy=1&wx_co=1" alt="图片"></p>
<p>大家对比看下，返回的就是每个分组的第一条数据<img src="https://mmbiz.qpic.cn/mmbiz_png/PoF8jo1Pmpw3lfjrm2iavcLbJViaxTt98DiaygageSOJ2vegzFd9376EnPCtT9PkqvXHfJW292RorMBld9bEN56pg/640?wx_fmt=png&wxfrom=5&wx_lazy=1&wx_co=1" alt="图片"></p>
<p>当然，平时大家使用的时候，group by还是配合聚合函数使用的，除非一些特殊场景，比如你想<strong>去重</strong>，当然去重用<code>distinct</code>也是可以的。</p>
<h3 id="4-2-group-by-后面跟的字段一定要出现在select中嘛。"><a href="#4-2-group-by-后面跟的字段一定要出现在select中嘛。" class="headerlink" title="4.2 group by 后面跟的字段一定要出现在select中嘛。"></a>4.2 group by 后面跟的字段一定要出现在select中嘛。</h3><p>不一定，比如以下SQL：</p>
<figure class="highlight csharp"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="function"><span class="keyword">select</span> <span class="title">max</span>(<span class="params">age</span>)  <span class="keyword">from</span> staff <span class="keyword">group</span> <span class="keyword">by</span> city</span>;</span><br></pre></td></tr></table></figure>

<p>执行结果如下：</p>
<p><img src="https://mmbiz.qpic.cn/mmbiz_png/PoF8jo1Pmpw3lfjrm2iavcLbJViaxTt98DkCukib9KOVs3bYlmZibOK5kzhUCS5NdUic0dMYhelwTITg40NKQfvibGHA/640?wx_fmt=png&wxfrom=5&wx_lazy=1&wx_co=1" alt="图片"></p>
<p>分组字段<code>city</code>不在select 后面，并不会报错。当然，这个可能跟<strong>不同的数据库，不同的版本</strong>有关吧。大家使用的时候，可以先验证一下就好。有一句话叫做，<strong>纸上得来终觉浅，绝知此事要躬行</strong>。</p>
<h2 id="4-3-group-by导致的慢SQL问题"><a href="#4-3-group-by导致的慢SQL问题" class="headerlink" title="4.3 group by导致的慢SQL问题"></a>4.3 <code>group by</code>导致的慢SQL问题</h2><p>到了最重要的一个注意问题啦，<code>group by</code>使用不当，很容易就会产生慢SQL 问题。因为它既用到<strong>临时表</strong>，又默认用到<strong>排序</strong>。有时候还可能用到<strong>磁盘临时表</strong>。</p>
<blockquote>
<ul>
<li>如果执行过程中，会发现内存临时表大小到达了<strong>上限</strong>（控制这个上限的参数就是<code>tmp_table_size</code>），会把<strong>内存临时表转成磁盘临时表</strong>。</li>
<li>如果数据量很大，很可能这个查询需要的磁盘临时表，就会占用大量的磁盘空间。</li>
</ul>
</blockquote>
<p>这些都是导致慢SQL的x因素，我们一起来探讨优化方案哈。</p>
<h2 id="5-group-by的一些优化方案"><a href="#5-group-by的一些优化方案" class="headerlink" title="5. group by的一些优化方案"></a>5. group by的一些优化方案</h2><p>从哪些方向去优化呢？</p>
<ul>
<li>方向1：既然它默认会排序，我们不给它排是不是就行啦。</li>
<li>方向2：既然临时表是影响group by性能的X因素，我们是不是可以不用临时表？</li>
</ul>
<p>我们一起来想下，执行<code>group by</code>语句为什么需要临时表呢？<code>group by</code>的语义逻辑，就是统计不同的值出现的个数。如果这个<strong>这些值一开始就是有序的</strong>，我们是不是直接往下扫描统计就好了，就不用<strong>临时表来记录并统计结果</strong>啦?</p>
<ul>
<li>group by 后面的字段加索引</li>
<li>order by null 不用排序</li>
<li>尽量只使用内存临时表</li>
<li>使用SQL_BIG_RESULT</li>
</ul>
<h3 id="5-1-group-by-后面的字段加索引"><a href="#5-1-group-by-后面的字段加索引" class="headerlink" title="5.1 group by 后面的字段加索引"></a>5.1 group by 后面的字段加索引</h3><p>如何保证<code>group by</code>后面的字段数值一开始就是有序的呢？当然就是<strong>加索引</strong>啦。</p>
<p>我们回到一下这个SQL</p>
<figure class="highlight axapta"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> city ,<span class="keyword">count</span>(*) <span class="keyword">as</span> num <span class="keyword">from</span> staff <span class="keyword">where</span> age= <span class="number">19</span> <span class="keyword">group</span> <span class="keyword">by</span> city;</span><br></pre></td></tr></table></figure>

<p>它的执行计划</p>
<p><img src="https://mmbiz.qpic.cn/mmbiz_png/PoF8jo1Pmpw3lfjrm2iavcLbJViaxTt98D5bI3xxzpRudNexl6sSoV8nNwdhgcsvzGoZev3oecHgFPTicmyzIiaARg/640?wx_fmt=png&wxfrom=5&wx_lazy=1&wx_co=1" alt="图片"></p>
<p>如果我们给它加个联合索引<code>idx_age_city（age,city）</code></p>
<figure class="highlight pgsql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> staff <span class="keyword">add</span> <span class="keyword">index</span> idx_age_city(age,city);</span><br></pre></td></tr></table></figure>

<p>再去看执行计划，发现既不用排序，也不需要临时表啦。<img src="https://mmbiz.qpic.cn/mmbiz_png/PoF8jo1Pmpw3lfjrm2iavcLbJViaxTt98DMD7yicibUeXwiaP5iaap3Zjt0F9yItK1bM9pQ4toN59zbd4K8oKWMHEeYQ/640?wx_fmt=png&wxfrom=5&wx_lazy=1&wx_co=1" alt="图片"></p>
<p><strong>加合适的索引</strong>是优化<code>group by</code>最简单有效的优化方式。</p>
<h3 id="5-2-order-by-null-不用排序"><a href="#5-2-order-by-null-不用排序" class="headerlink" title="5.2 order by null 不用排序"></a>5.2 order by null 不用排序</h3><p>并不是所有场景都适合加索引的，如果碰上不适合创建索引的场景，我们如何优化呢？</p>
<blockquote>
<p>如果你的需求并不需要对结果集进行排序，可以使用<code>order by null</code>。</p>
</blockquote>
<figure class="highlight axapta"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> city ,<span class="keyword">count</span>(*) <span class="keyword">as</span> num <span class="keyword">from</span> staff <span class="keyword">group</span> <span class="keyword">by</span> city <span class="keyword">order</span> <span class="keyword">by</span> <span class="literal">null</span></span><br></pre></td></tr></table></figure>

<p>执行计划如下，已经没有<code>filesort</code>啦</p>
<p><img src="https://mmbiz.qpic.cn/mmbiz_png/PoF8jo1Pmpw3lfjrm2iavcLbJViaxTt98DiaxicR4nic6laxXgrVFJAGwq5lU7FJSvRicZJKP6lGUxONek0tKPtAYAfA/640?wx_fmt=png&wxfrom=5&wx_lazy=1&wx_co=1" alt="图片"></p>
<h3 id="5-3-尽量只使用内存临时表"><a href="#5-3-尽量只使用内存临时表" class="headerlink" title="5.3 尽量只使用内存临时表"></a>5.3 尽量只使用内存临时表</h3><p>如果<code>group by</code>需要统计的数据不多，我们可以尽量只使用<strong>内存临时表</strong>；因为如果group by 的过程因为内存临时表放不下数据，从而用到磁盘临时表的话，是比较耗时的。因此可以适当调大<code>tmp_table_size</code>参数，来避免用到<strong>磁盘临时表</strong>。</p>
<h3 id="5-4-使用SQL-BIG-RESULT优化"><a href="#5-4-使用SQL-BIG-RESULT优化" class="headerlink" title="5.4 使用SQL_BIG_RESULT优化"></a>5.4 使用SQL_BIG_RESULT优化</h3><p>如果数据量实在太大怎么办呢？总不能无限调大<code>tmp_table_size</code>吧？但也不能眼睁睁看着数据先放到内存临时表，<strong>随着数据插入</strong>发现到达上限，再转成磁盘临时表吧？这样就有点不智能啦。</p>
<p>因此，如果预估数据量比较大，我们使用<code>SQL_BIG_RESULT</code> 这个提示直接用磁盘临时表。MySQl优化器发现，磁盘临时表是B+树存储，存储效率不如数组来得高。因此会直接用数组来存</p>
<p>示例SQl如下：</p>
<figure class="highlight axapta"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> SQL_BIG_RESULT city ,<span class="keyword">count</span>(*) <span class="keyword">as</span> num <span class="keyword">from</span> staff <span class="keyword">group</span> <span class="keyword">by</span> city;</span><br></pre></td></tr></table></figure>

<p>执行计划的<code>Extra</code>字段可以看到，执行没有再使用临时表，而是只有排序<img src="https://mmbiz.qpic.cn/mmbiz_png/PoF8jo1Pmpw3lfjrm2iavcLbJViaxTt98DxE8wviaAbjPONoicfnXhdaDWKrqOWm60JfPvlBCgdO5ib8Nw6wiavEUA1w/640?wx_fmt=png&wxfrom=5&wx_lazy=1&wx_co=1" alt="图片"></p>
<p>执行流程如下：</p>
<ol>
<li>初始化 sort_buffer，放入city字段；</li>
<li>扫描表staff，依次取出city的值,存入 sort_buffer 中；</li>
<li>扫描完成后，对 sort_buffer的city字段做排序</li>
<li>排序完成后，就得到了一个有序数组。</li>
<li>根据有序数组，统计每个值出现的次数。</li>
</ol>

    </div>
<div>
  
    <div>
    
        <div style="text-align:center;color: #ccc;font-size:14px;">-------------本文结束<i class="fa fa-paw"></i>感谢您的阅读-------------</div>
    
</div>

  
</div>

    
    
    
        

<div>
<ul class="post-copyright">
  <li class="post-copyright-author">
    <strong>本文作者： </strong>
  </li>
  <li class="post-copyright-link">
    <strong>本文链接：</strong>
    <a href="https://wan-lei8.gitee.io/2022/03/10/group-by-%E8%AF%A6%E8%A7%A3/" title="group by 详解">https://wan-lei8.gitee.io/2022/03/10/group-by-详解/</a>
  </li>
  <li class="post-copyright-license">
    <strong>版权声明： </strong>本博客所有文章除特别声明外，均采用 <a href="https://creativecommons.org/licenses/by-nc-sa/4.0/deed.zh" rel="noopener" target="_blank"><i class="fab fa-fw fa-creative-commons"></i>BY-NC-SA</a> 许可协议。转载请注明出处！
  </li>
</ul>
</div>


      <footer class="post-footer">

        


        
    <div class="post-nav">
      <div class="post-nav-item">
    <a href="/2022/03/10/order-by-%E8%AF%A6%E8%A7%A3/" rel="prev" title="order by 详解">
      <i class="fa fa-chevron-left"></i> order by 详解
    </a></div>
      <div class="post-nav-item">
    <a href="/2022/03/10/%E6%B7%B1%E5%88%86%E9%A1%B5%E9%97%AE%E9%A2%98/" rel="next" title="深分页问题">
      深分页问题 <i class="fa fa-chevron-right"></i>
    </a></div>
    </div>
      </footer>
    
  </article>
  
  
  



          </div>
          

<script>
  window.addEventListener('tabs:register', () => {
    let { activeClass } = CONFIG.comments;
    if (CONFIG.comments.storage) {
      activeClass = localStorage.getItem('comments_active') || activeClass;
    }
    if (activeClass) {
      let activeTab = document.querySelector(`a[href="#comment-${activeClass}"]`);
      if (activeTab) {
        activeTab.click();
      }
    }
  });
  if (CONFIG.comments.storage) {
    window.addEventListener('tabs:click', event => {
      if (!event.target.matches('.tabs-comment .tab-content .tab-pane')) return;
      let commentClass = event.target.classList[1];
      localStorage.setItem('comments_active', commentClass);
    });
  }
</script>

        </div>
          
  
  <div class="toggle sidebar-toggle">
    <span class="toggle-line toggle-line-first"></span>
    <span class="toggle-line toggle-line-middle"></span>
    <span class="toggle-line toggle-line-last"></span>
  </div>

  <aside class="sidebar">
    <div class="sidebar-inner">

      <ul class="sidebar-nav motion-element">
        <li class="sidebar-nav-toc">
          文章目录
        </li>
        <li class="sidebar-nav-overview">
          站点概览
        </li>
      </ul>

      <!--noindex-->
      <div class="post-toc-wrap sidebar-panel">
          <div class="post-toc motion-element"><ol class="nav"><li class="nav-item nav-level-2"><a class="nav-link" href="#1-%E4%BD%BF%E7%94%A8group-by%E7%9A%84%E7%AE%80%E5%8D%95%E4%BE%8B%E5%AD%90"><span class="nav-number">1.</span> <span class="nav-text">1. 使用group by的简单例子</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#2-group-by-%E5%8E%9F%E7%90%86%E5%88%86%E6%9E%90"><span class="nav-number">2.</span> <span class="nav-text">2. group by 原理分析</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#2-1-explain-%E5%88%86%E6%9E%90"><span class="nav-number">2.1.</span> <span class="nav-text">2.1 explain 分析</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#2-2-group-by-%E7%9A%84%E7%AE%80%E5%8D%95%E6%89%A7%E8%A1%8C%E6%B5%81%E7%A8%8B"><span class="nav-number">2.2.</span> <span class="nav-text">2.2 group by 的简单执行流程</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#3-where-%E5%92%8C-having%E7%9A%84%E5%8C%BA%E5%88%AB"><span class="nav-number">3.</span> <span class="nav-text">3. where 和 having的区别</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#3-1-group-by-where-%E7%9A%84%E6%89%A7%E8%A1%8C%E6%B5%81%E7%A8%8B"><span class="nav-number">3.1.</span> <span class="nav-text">3.1 group by + where 的执行流程</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#3-2-group-by-having-%E7%9A%84%E6%89%A7%E8%A1%8C"><span class="nav-number">3.2.</span> <span class="nav-text">3.2 group by + having 的执行</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#3-3-%E5%90%8C%E6%97%B6%E6%9C%89where%E3%80%81group-by-%E3%80%81having%E7%9A%84%E6%89%A7%E8%A1%8C%E9%A1%BA%E5%BA%8F"><span class="nav-number">3.3.</span> <span class="nav-text">3.3 同时有where、group by 、having的执行顺序</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#3-4-where-having-%E5%8C%BA%E5%88%AB%E6%80%BB%E7%BB%93"><span class="nav-number">3.4.</span> <span class="nav-text">3.4 where + having 区别总结</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#4-%E4%BD%BF%E7%94%A8-group-by-%E6%B3%A8%E6%84%8F%E7%9A%84%E9%97%AE%E9%A2%98"><span class="nav-number">4.</span> <span class="nav-text">4. 使用 group by 注意的问题</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#4-1-group-by%E4%B8%80%E5%AE%9A%E8%A6%81%E9%85%8D%E5%90%88%E8%81%9A%E5%90%88%E5%87%BD%E6%95%B0%E4%BD%BF%E7%94%A8%E5%98%9B%EF%BC%9F"><span class="nav-number">4.1.</span> <span class="nav-text">4.1 group by一定要配合聚合函数使用嘛？</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#4-2-group-by-%E5%90%8E%E9%9D%A2%E8%B7%9F%E7%9A%84%E5%AD%97%E6%AE%B5%E4%B8%80%E5%AE%9A%E8%A6%81%E5%87%BA%E7%8E%B0%E5%9C%A8select%E4%B8%AD%E5%98%9B%E3%80%82"><span class="nav-number">4.2.</span> <span class="nav-text">4.2 group by 后面跟的字段一定要出现在select中嘛。</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#4-3-group-by%E5%AF%BC%E8%87%B4%E7%9A%84%E6%85%A2SQL%E9%97%AE%E9%A2%98"><span class="nav-number">5.</span> <span class="nav-text">4.3 group by导致的慢SQL问题</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#5-group-by%E7%9A%84%E4%B8%80%E4%BA%9B%E4%BC%98%E5%8C%96%E6%96%B9%E6%A1%88"><span class="nav-number">6.</span> <span class="nav-text">5. group by的一些优化方案</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#5-1-group-by-%E5%90%8E%E9%9D%A2%E7%9A%84%E5%AD%97%E6%AE%B5%E5%8A%A0%E7%B4%A2%E5%BC%95"><span class="nav-number">6.1.</span> <span class="nav-text">5.1 group by 后面的字段加索引</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#5-2-order-by-null-%E4%B8%8D%E7%94%A8%E6%8E%92%E5%BA%8F"><span class="nav-number">6.2.</span> <span class="nav-text">5.2 order by null 不用排序</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#5-3-%E5%B0%BD%E9%87%8F%E5%8F%AA%E4%BD%BF%E7%94%A8%E5%86%85%E5%AD%98%E4%B8%B4%E6%97%B6%E8%A1%A8"><span class="nav-number">6.3.</span> <span class="nav-text">5.3 尽量只使用内存临时表</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#5-4-%E4%BD%BF%E7%94%A8SQL-BIG-RESULT%E4%BC%98%E5%8C%96"><span class="nav-number">6.4.</span> <span class="nav-text">5.4 使用SQL_BIG_RESULT优化</span></a></li></ol></li></ol></div>
      </div>
      <!--/noindex-->

      <div class="site-overview-wrap sidebar-panel">
        <div class="site-author motion-element" itemprop="author" itemscope itemtype="http://schema.org/Person">
    <img class="site-author-image" itemprop="image" alt=""
      src="/images/avatar.png">
  <p class="site-author-name" itemprop="name"></p>
  <div class="site-description" itemprop="description"></div>
</div>
<div class="site-state-wrap motion-element">
  <nav class="site-state">
      <div class="site-state-item site-state-posts">
          <a href="/archives">
          <span class="site-state-item-count">41</span>
          <span class="site-state-item-name">日志</span>
        </a>
      </div>
  </nav>
</div>
  <div class="links-of-author motion-element">
      <span class="links-of-author-item">
        <a href="https://github.com/yourname" title="GitHub → https:&#x2F;&#x2F;github.com&#x2F;yourname" rel="noopener" target="_blank"><i class="fab fa-github fa-fw"></i>GitHub</a>
      </span>
      <span class="links-of-author-item">
        <a href="/malegod958@163.com" title="E-Mail → malegod958@163.com"><i class="fa fa-envelope fa-fw"></i>E-Mail</a>
      </span>
  </div>
  <div class="cc-license motion-element" itemprop="license">
    <a href="https://creativecommons.org/licenses/by-nc-sa/4.0/deed.zh" class="cc-opacity" rel="noopener" target="_blank"><img src="/images/cc-by-nc-sa.svg" alt="Creative Commons"></a>
  </div>



      </div>

    </div>
  </aside>
  <div id="sidebar-dimmer"></div>


      </div>
    </main>

    <footer class="footer">
      <div class="footer-inner">
        

        

<div class="copyright">
  
  &copy; 2018 – 
  <span itemprop="copyrightYear">2022</span>
  <span class="with-love">
    <i class="heart"></i>
  </span>
  <span class="author" itemprop="copyrightHolder">万磊</span>
</div>
  <div class="powered-by">

  </div>

        








      </div>
    </footer>
  </div>

  
  
  <script color='' opacity='0.5' zIndex='-1' count='99' src="/lib/canvas-nest/canvas-nest.min.js"></script>
  <script src="/lib/anime.min.js"></script>
  <script src="/lib/velocity/velocity.min.js"></script>
  <script src="/lib/velocity/velocity.ui.min.js"></script>

<script src="/js/utils.js"></script>

<script src="/js/motion.js"></script>


<script src="/js/schemes/pisces.js"></script>


<script src="/js/next-boot.js"></script>




  















  

  

  
	<script type="text/javascript" src="//cdn.bootcss.com/canvas-nest.js/1.0.0/canvas-nest.min.js"></script>
	

<script src="/live2dw/lib/L2Dwidget.min.js?094cbace49a39548bed64abff5988b05"></script><script>L2Dwidget.init({"log":false,"pluginJsPath":"lib/","pluginModelPath":"assets/","pluginRootPath":"live2dw/","tagMode":false});</script></body>
</html>
<!-- 页面点击小红心 -->
<script type="text/javascript" src="/js/src/clicklove.js"></script>
